import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;

/**
 * Created with IntelliJ IEDA.
 * Description:
 * User:86186
 * Date:2022-08-15
 * Time:22:23
 */
public class HomeWorkSelect {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("wgz20030828");

        //连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);


        // ? 作为占位符. 占个位置, 后面会替换成其他的值.
        String sql = "SELECT bk.NAME as book_name,bk.author as book_author,s.NAME as student_name,bi.start_time,bi.end_time" +

                " FROM borrow_info as bi JOIN book as bk ON bi.book_id = bk.id JOIN category as c ON bk.category_id = c.id" +

                " JOIN student as s ON bi.student_id = s.id WHERE c.NAME = ?";
        //预处理
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.setInt(1,1);

        //    查询语句要使用 executeQuery 来完成了.
        //    返回的结果是 ResultSet . 结果集. 里面是一个 "表" 这样的数据结构.
        //    一个表里有很多行, 每一行有很多列~~



        preparedStatement.setString(1, "计算机");
        ResultSet resultSet = preparedStatement.executeQuery();

        while(resultSet.next()){

            String  bookName  = resultSet.getString("book_name");

            String  bookAuthor = resultSet.getString("book_author");

            String  studentName = resultSet.getString("student_name");

            Timestamp startTime  = resultSet.getTimestamp("start_time");

            Timestamp  endTime   = resultSet.getTimestamp("end_time");

            System.out.println(String.format("书名：%s，作者：%s，借阅者：%s，" +

                            "借阅起始日期：%s，结束日期：%s", bookName, bookAuthor, studentName, startTime, endTime));

        }

        // 6. 释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();


    }
}




